package com.idanchuang.database.manage.service.impl;

import com.idanchuang.component.base.JsonResult;
import com.idanchuang.component.base.page.PageData;
import com.idanchuang.component.base.util.DateUtil;
import com.idanchuang.database.manage.model.MenuList;
import com.idanchuang.database.manage.model.PageInput;
import com.idanchuang.database.manage.service.CrudService;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.Timestamp;
import java.util.*;

/**
 * @param
 * @author:zhangmengsi
 * @description:
 * @date:Created in 16:50 2021/2/26
 */
@Service
public class CrudServiceImpl implements CrudService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<MenuList> getMenuList() {
        List<MenuList> menuLists = new ArrayList<>();
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("show databases");
        List<String> dbNames = new ArrayList<>(maps.size());
        for (int i = 0; i < maps.size(); i++) {
            dbNames.add((String) maps.get(i).get("Database"));
        }
        for (String db : dbNames) {
            MenuList m = new MenuList();
            List<Map<String, Object>> tableList = jdbcTemplate.queryForList("select table_name " +
                    "from information_schema.tables where table_schema=? and table_type='base table';", db);
            List<String> tableName = new ArrayList<>(tableList.size());
            for (int i = 0; i < tableList.size(); i++) {
                tableName.add((String) tableList.get(i).get("table_name"));
            }
            m.setDatabase(db);
            m.setTables(tableName);
            menuLists.add(m);
        }
        return menuLists;
    }

    @Override
    public PageData<Map<String, Object>> getAllTableInfo(Integer current, Integer size, String database, String table) {
        jdbcTemplate.execute("use " + database + ";");
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from " + table);
        // 截取
        int start = (current - 1) * size;
        int end = start + size;
        List<Map<String, Object>> res = maps.subList(start, (end < maps.size()) ? end : maps.size());
        for (Map<String, Object> e : res) {
            for (String key : e.keySet()) {
                Object obj = e.get(key);
                if(obj instanceof Date){
                    e.put(key, DateUtil.convert2String((Date) obj, DateUtil.DF_YYYY_MM_DD_HH_MM_SS));
                }
            }
        }
//        Class<? extends Object> keyClass = null;
//        Set set = res.get(0).entrySet();
//        Iterator it=set.iterator();
//        while(it.hasNext()) {
//            Map.Entry entry=(Map.Entry)it.next();
//            keyClass = entry.getValue().getClass();
//            if (keyClass.equals(Timestamp.class)||keyClass.equals(Date.class)||keyClass.equals(DateTime.class)) {
//                for (Map<String,Object> k : res){
//                    k.put((String) entry.getKey(),DateUtil.convert2String((Date)k.get(entry.getKey()),"yyyy-MM-dd HH:mm:ss"));
//                }
//            }
//        }
        PageData<Map<String, Object>> listPageData = new PageData<>(res, current, size,
                maps.size());
        return listPageData;
    }

    @Override
    public List<Map<String, Object>> getAllField(String database,String tableName) {
        jdbcTemplate.execute("use " + database + ";");
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from INFORMATION_SCHEMA.Columns where table_name = ?;", tableName);
        return maps;
    }

    @Override
    public JsonResult<Object> executeSql(String database, String sql, PageInput input) {
        JsonResult<Object> result = JsonResult.success();
        String operateType = sql.trim().split(" ")[0];
        List<Map<String, Object>> maps = new ArrayList<>();
        int rows;
        int start = (input.getCurrent() - 1) * input.getSize();
        int end = start + input.getSize();
        jdbcTemplate.execute("use " + database);
        switch (operateType) {
            case "select":
                maps = jdbcTemplate.queryForList(sql);
                List<Map<String, Object>> res = maps.subList(start, (end < maps.size()) ? end : maps.size());
                for (Map<String, Object> e : res) {
                    for (String key : e.keySet()) {
                        Object obj = e.get(key);
                        if(obj instanceof Date){
                            e.put(key, DateUtil.convert2String((Date) obj, DateUtil.DF_YYYY_MM_DD_HH_MM_SS));
                        }
                    }
                }
                PageData<Map<String, Object>> pageData = new PageData<>(res, input.getCurrent(), input.getSize(),
                        maps.size());
                result.setData(pageData);
                return result;
            case "update":
            case "delete":
            case "insert":
                rows = jdbcTemplate.update(sql);
                result.setData(rows);
                return result;
            default:
                try {
                    jdbcTemplate.execute(sql);
                } catch (Exception e) {
                    e.printStackTrace();
                }
        }
        return result;
    }
}
